package com.hehe.controller;


import com.hehe.DTO.Tb_sql_test_DTO;
import com.hehe.Dao.Select_Mapper;
import com.hehe.POJO.Tb_sql_test;
import org.json.JSONException;
import org.json.JSONObject;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;
import org.springframework.stereotype.Controller;
import org.springframework.ui.Model;
import org.springframework.web.bind.annotation.*;

import javax.servlet.http.HttpServletRequest;
import java.util.*;


@Controller
@RequestMapping(value = "/id")
public class Get_by_id {

    @Value("${file.upload.path}")
    private String path;

    @Autowired(required = false)
    private Select_Mapper select_mapper;

    @Autowired
    private JdbcTemplate jdbcTemplate;

//    SimpleDateFormat sdf = new SimpleDateFormat("yyyy/MM/dd/");
    /*
    <form  method="post"   action="/upload2"  enctype="multipart/form-data">
        <input type="file" name="uploadFiles" value="请选择文件" multiple>
        <input type="submit" value="上传">
    </form>






     */


    @PostMapping(value = "/drop_and_creat_database")
    @ResponseBody
    public String edit(Model model, @RequestBody String postObj) throws JSONException {
        JSONObject jsonobj = new JSONObject(postObj);
        Object sql = jsonobj.get("sql");
        String test_sql = sql.toString();
        System.out.println(test_sql);
        jdbcTemplate.execute(test_sql);


//        String dropTableSql = "DROP TABLE IF EXISTS login";
//        jdbcTemplate.execute(dropTableSql);
//        String createTableSql = "CREATE TABLE login (" +
//                "id int(4) NOT NULL," +
//                "user_id int(4) NOT NULL," +
//                "client_id int(4) NOT NULL," +
//                "date date NOT NULL," +
//                "PRIMARY KEY (id))";
//        jdbcTemplate.execute(createTableSql);



//        String sql3 = "DROP TABLE IF EXISTS login;" +
//                "CREATE TABLE login (" +
//                "id int(4) NOT NULL," +
//                "user_id int(4) NOT NULL," +
//                "client_id int(4) NOT NULL," +
//                "date date NOT NULL," +
//                "PRIMARY KEY (id));";
//        jdbcTemplate.execute(sql3);



        return "";
    }


    /*

ysql://localhost:3306/myschool?characterEncoding=utf-8&allowMultiQueries=true


     */




    @GetMapping(value = "/test")
    public String index(Model model, HttpServletRequest request, Integer id, String sql, String type){
        System.out.println("id = " + id);
        System.out.println("sql = " + sql);
        System.out.println("type = " + type);

        sql = sql.replaceAll(" ", "+");
        System.out.println("sql = " + sql);
        byte[] decodedBytes = Base64.getDecoder().decode(sql);
        String base64_sql = new String(decodedBytes);
        System.out.println("解码后的字符串： " + base64_sql);
        ArrayList<Object> arrayList = new ArrayList<>();
        if(sql == null || sql.equals("")){
            model.addAttribute("result_null" , -1 );
            model.addAttribute("column" , arrayList);
            model.addAttribute("sql" , "");
        }else {
            ArrayList<Collection<Object>> valuesList = new ArrayList<>();
//        sql = "select  * from user_profile  ";
            try {
                List<Map<String, Object>> results = jdbcTemplate.queryForList(base64_sql);
                for (int i = 0; i < results.size(); i++) {
                    Map<String, Object> objectMap = results.get(i);
                    Collection<Object> values = objectMap.values();
                    valuesList.add(values);
//                System.out.println(123);
                }
                if(results.size() == 0){
                    model.addAttribute("result_null" , -1 );
                    model.addAttribute("column" , arrayList);
                    model.addAttribute("sql" , sql);
                }
                model.addAttribute("results" , valuesList);
                model.addAttribute("column" , results.get(0).keySet());
                model.addAttribute("sql" , sql);
            } catch (Exception e) {
                // 处理异常信息
                System.out.println("SQL异常信息: " + e.getMessage()); // SQLSyntaxErrorException
                model.addAttribute("SQLSyntaxErrorException" , e.getMessage());
            }
        }
        System.out.println(id);
        Tb_sql_test_DTO tb_sql_test_dto = new Tb_sql_test_DTO();
        Tb_sql_test tb_sql_test = null;
        if (type != null){

        }else {
            tb_sql_test = select_mapper.get_by_id(id);
        }
        assert tb_sql_test != null;
        String img_urls = tb_sql_test.getImg_urls();
        String[] file_name_listtt = img_urls.split(",");
        ArrayList<String> arrayList111 = new ArrayList<>();

        for (String file_name : file_name_listtt) {
            if (file_name.trim().length() > 5){
                String filePath = request.getScheme() + "://" + request.getServerName() + ":" + request.getServerPort() + "/SQL_IMG/" + file_name.trim();
                System.out.println("filePath = " + filePath);
                arrayList111.add(filePath);
            }
        }
        /*    `id`   `title`   `page_url`  `level`  `img_urls`    `describe`  `sql`  `answer`    */
        tb_sql_test_dto.setId(tb_sql_test.getId());
        tb_sql_test_dto.setTitle(tb_sql_test.getTitle());
        tb_sql_test_dto.setPage_url(tb_sql_test.getPage_url());
        tb_sql_test_dto.setLevel(tb_sql_test.getLevel());
        tb_sql_test_dto.setImg_url_list(arrayList111);
        tb_sql_test_dto.setDescribe(tb_sql_test.getDescribe());
        tb_sql_test_dto.setSql(tb_sql_test.getSql());
        tb_sql_test_dto.setAnswer(tb_sql_test.getAnswer());
        System.out.println("");
        model.addAttribute("tb_sql_test_dto", tb_sql_test_dto);
        return "select/get_by_id.html";
    }




}

